# @Desc    : 数据库数据同步工具
# @Author  : meihongliang
# @Time    : 2025-04-01 21:32
import time
import pymysql
from pymysql import cursors
from typing import List, Dict, Optional, Any

# 常量定义
DB_NAMES = ['appfactory', 'app_a2b', 'app_a3b']
TABLE_NAME = 'your_table_name'
QUERY_CONDITION = 'WHERE status = 1 LIMIT 30'
DELETE_CONDITION = 'WHERE date < "2023-01-01"'


class DatabaseConfig:
    """数据库配置类"""

    def __init__(self):
        self.base_config = {
            'host': 'localhost',
            'user': 'username',
            'password': 'password',
            'charset': 'utf8mb4'
        }
        self.environments = {
            "1": {"mysql_port": 3306},
            "2": {"mysql_port": 3307},
            "3": {"mysql_port": 3308}
        }

    def get_db_config(self, env_id: str, db_name: str) -> Dict[str, Any]:
        """获取完整的数据库配置"""
        config = self.base_config.copy()
        config.update({
            'port': self.environments[env_id]['mysql_port'],
            'db': db_name
        })
        return config


class SQLBuilder:
    """SQL语句构建器"""

    @staticmethod
    def get_select_sql(db_name: str) -> Optional[str]:
        """获取查询SQL"""
        sql_map = {
            'appfactory': f"SELECT * FROM {db_name} {QUERY_CONDITION}",
            'crimi': f"SELECT * FROM {db_name} {QUERY_CONDITION}",
            'sc_sflow': f"SELECT * FROM {db_name} {QUERY_CONDITION}"
        }
        return sql_map.get(db_name)

    @staticmethod
    def get_delete_sql(db_name: str) -> Optional[str]:
        """获取删除SQL"""
        if db_name == 'appfactory':
            return f"DELETE FROM {db_name} {DELETE_CONDITION}"
        return None


class DataModifier:
    """数据修改器"""

    @staticmethod
    def modify_data(data: List[Dict], db_name: str) -> List[Dict]:
        """根据数据库名修改数据"""
        if not data:
            return data

        modifications = {
            'appfactory': lambda x: "[前缀]" + x,
            'app_a2b': lambda x: "[前缀]" + x,
            'app_a3b': lambda x: "[前缀]" + x
        }

        modifier = modifications.get(db_name)
        if modifier:
            for item in data:
                if 'name' in item:
                    item['name'] = modifier(item['name'])
        return data


class DatabaseSync:
    """数据库同步工具"""

    def __init__(self):
        self.config = DatabaseConfig()
        self.sql_builder = SQLBuilder()
        self.data_modifier = DataModifier()

    def get_source_data(self, env_id: str, db_name: str) -> Optional[List[Dict]]:
        """从源数据库获取数据"""
        db_config = self.config.get_db_config(env_id, db_name)
        print(f'\t获取当前(模版/标准)数据: {db_config}')

        try:
            conn = pymysql.connect(**db_config, cursorclass=cursors.DictCursor)
            with conn.cursor() as cursor:
                sql = self.sql_builder.get_select_sql(db_name)
                if not sql:
                    print(f'\t{db_name} 没有查询SQL')
                    return None

                cursor.execute(sql)
                data = cursor.fetchall()
                print(f"从环境{env_id}的{db_name}获取到{len(data)}条数据")
                self._print_data_details(data)
                return data
        except Exception as e:
            print(f"\t从环境{env_id}获取数据失败: {e}")
            return None
        finally:
            if 'conn' in locals() and conn.open:
                conn.close()

    def sync_to_target(self, source_data: List[Dict], target_env: str, target_db: str):
        """同步数据到目标数据库"""
        if not source_data:
            print("\t没有可同步的数据")
            return

        target_config = self.config.get_db_config(target_env, target_db)
        print(f'\t目标数据库连接信息: {target_config}')

        columns = list(source_data[0].keys())
        columns_str = ', '.join(columns)
        placeholders = ', '.join(['%s'] * len(columns))

        try:
            conn = pymysql.connect(**target_config)
            with conn.cursor() as cursor:
                # 删除旧数据
                delete_sql = self.sql_builder.get_delete_sql(target_db)
                if delete_sql:
                    cursor.execute(delete_sql)
                    print(f"\t已删除{target_db}中{cursor.rowcount}条旧数据")
                else:
                    print(f'\t{target_db} 不需要删除数据')

                # 插入新数据
                insert_sql = f"INSERT IGNORE INTO {target_db} ({columns_str}) VALUES ({placeholders})"
                cursor.executemany(insert_sql, [tuple(item.values()) for item in source_data])
                conn.commit()
                print(f"\t成功同步 {cursor.rowcount} 条数据到 {target_db}")
        except Exception as e:
            print(f"\t同步到{target_db}失败: {e}")
            if 'conn' in locals() and conn.open:
                conn.rollback()
        finally:
            if 'conn' in locals() and conn.open:
                conn.close()

    def _print_data_details(self, data: List[Dict]):
        """打印数据详情"""
        if not data:
            return

        print("\n===== 数据内容详情 =====")
        for i, record in enumerate(data, 1):
            print(f"\n记录 #{i}:")
            for field, value in record.items():
                print(f"  {field}: {value}")
            print("-" * 30)


def main():
    """主程序"""
    sync_tool = DatabaseSync()
    env_ids = list(sync_tool.config.environments.keys())

    print(f'可用环境: {env_ids}')
    source_env = input("请选择标准环境: ")

    if source_env not in env_ids:
        raise ValueError("无效的环境选择")

    print(f'您选择的标准环境: {source_env}')
    target_envs = [env for env in env_ids if env != source_env]
    print(f'需要同步的目标环境: {target_envs}')

    for target_env in target_envs:
        print(f'\n开始同步到环境 {target_env}')
        for db_num, db_name in enumerate(DB_NAMES, 1):
            print(f'\n处理数据库 {db_num}/{len(DB_NAMES)}: {db_name}')

            # 获取并修改数据
            source_data = sync_tool.get_source_data(source_env, db_name)
            modified_data = sync_tool.data_modifier.modify_data(source_data, db_name)

            # 同步数据
            if modified_data:
                sync_tool.sync_to_target(modified_data, target_env, db_name)
            else:
                print(f'\t{db_name} 无数据可同步')

            print(f'\t{db_name} 处理完成')

        time.sleep(2)
        print(f'\n环境 {target_env} 同步完成')

    print('\n所有目标环境同步完成')


if __name__ == '__main__':
    main()
